﻿using Dapper;
using HtmlAgilityPack;
using MarsCrawler.DataModels;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Net.Http;
using System.Threading;
using System.Threading.Tasks;

namespace MarsCrawler
{

	public enum DataBaseType
	{
		SQLite,MySQL
	}

	public class Crawler
	{

		static DataBaseType dbType = DataBaseType.SQLite;

		static string urlRoot = "https://www.haolizi.net/examples/csharp_{0}.html";

		/// <summary>
		/// 获取数据库连接对象
		/// </summary>
		/// <param name="dbType"></param>
		/// <returns></returns>
		public static IDbConnection GetDbConn(DataBaseType dbType)
		{
			string connStrSqlite = string.Format("Data Source={0}/example_db.sqlite", System.IO.Directory.GetCurrentDirectory());
			string connStrMysql = string.Format("server=127.0.0.1;port=3506;database=exampledb;userid=dahlin;password=123456") ;
			Dictionary<DataBaseType, IDbConnection> dbConnDic = new Dictionary<DataBaseType, IDbConnection>()
			{
				{DataBaseType.SQLite ,new SQLiteConnection(connStrSqlite) },
				{ DataBaseType.MySQL,new MySqlConnection(connStrMysql) },
			};
			return dbConnDic[dbType];
		}

		/// <summary>
		/// 获取html页面
		/// </summary>
		/// <param name="requestUrl">url地址</param>
		/// <returns></returns>
		public static async Task<string> HtmlRequest(string requestUrl)
		{
			HttpClient httpClient = new HttpClient();
			httpClient.DefaultRequestHeaders.Add("Method", "Get");
			httpClient.DefaultRequestHeaders.Add("KeepAlive", "false");   // HTTP KeepAlive设为false，防止HTTP连接保持
			httpClient.DefaultRequestHeaders.Add("UserAgent", "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.95 Safari/537.11");
			var response = await httpClient.GetStringAsync(requestUrl);
			return response;
		}

		/// <summary>
		/// 解析提取字段
		/// </summary>
		/// <param name="htmlStr"></param>
		/// <returns></returns>
		public  static List<ExampleModel> GetExampleData(string htmlStr)
		{

			#region 字段
			string rootUrl = @"https://www.haolizi.net";
			string downloadUrl = string.Empty;

			#endregion

			List<ExampleModel> examModels = new List<ExampleModel>();
			HtmlDocument htmlDoc = new HtmlDocument();

			htmlDoc.LoadHtml(htmlStr);
			var liNodes = htmlDoc.DocumentNode.SelectNodes("//div[@class='content-box']/ul/li");

			foreach(HtmlNode node in liNodes)
			 {

				List<string> tags = new List<string>();

				#region 提取元素
				HtmlNode aNode = node.SelectSingleNode("./div[@class='baseinfo']/h3/a");
				string name = aNode.InnerText;
				string detailUrl = rootUrl + aNode.Attributes["href"].Value;

				HtmlNode categoryNode = node.SelectSingleNode("./div[@class='baseinfo']/a");
				string category = categoryNode.InnerText;
				string categoryUrl = rootUrl + categoryNode.Attributes["href"].Value;

				HtmlNode hotNumNode = node.SelectSingleNode("./div[@class='baseinfo']/div[@class='xj']/span[@class='rq']/em");
				int hotNum = Convert.ToInt32(hotNumNode.InnerText);

				HtmlNode downloadCountNode = node.SelectSingleNode("./div[@class='baseinfo']/div[@class='xj']/span[2]");
				int downloadCount = Convert.ToInt32(downloadCountNode.InnerText);

				HtmlNode needScoreNode = node.SelectSingleNode("./div[@class='baseinfo']/div[@class='xj']/span[3]");
				int needScore = Convert.ToInt32(needScoreNode.InnerText);

				HtmlNode devLanguageNode = node.SelectSingleNode("./div[@class='sinfo']/div/p[@class='fun']/span[1]");
				string devLanguage = devLanguageNode.NextSibling.InnerText.Replace("&nbsp;", "").Replace("|", "");

				HtmlNode downloadSizeNode = node.SelectSingleNode("./div[@class='sinfo']/div/p[@class='fun']/span[2]");
				string downloadSize = downloadSizeNode.InnerText;

				HtmlNode pubdateNode = node.SelectSingleNode("./div[@class='sinfo']/div/p[@class='fun']/span[3]");
				string pubdate = pubdateNode.InnerText;

				HtmlNode pubPersionNode = node.SelectSingleNode("./div[@class='sinfo']/div/p[@class='fun']/span[4]/a");
				string pubPersion = pubPersionNode.InnerText;

				var tagNodes = node.SelectNodes("./div[@class='sinfo']/div/p[@class='fun']/span[contains(@class , 'zwch')]");
				 if (tagNodes != null)
				 {
					 foreach (var tnode in tagNodes)
					 {

						 tags.Add(tnode.SelectSingleNode("./a").InnerText);
						 Console.WriteLine(name + " tag:" + tnode.SelectSingleNode("./a").InnerText);
					 }
				 }
				#endregion

				ExampleModel examModel = new ExampleModel()
				{
					Name = name,
					DetailUrl = detailUrl,
					Category = category,
					CategoryUrl = categoryUrl,
					DevLanguage = devLanguage,
					DownloadCount = downloadCount,
					DownloadSize = downloadSize.Replace("大小：","").Trim(),
					DownloadUrl = downloadUrl,
					HotNum = hotNum,
					NeedScore = needScore,
					Pubdate = Convert.ToDateTime(pubdate.Replace("发布时间：","").Trim()),
					 PubPersion = pubPersion,
					 Tags = tags
				 };

				examModels.Add(examModel);

			 };

			return examModels;
		}


		/// <summary>
		/// 数据输入
		/// </summary>
		/// <param name="examModel">模型数据</param>
		/// <returns></returns>
		public static int InsertDataBase(ExampleModel examModel)
		{
			int result = -1;
			IDbConnection dbConn = GetDbConn(dbType);
			dbConn.Open();
			IDbTransaction transaction = dbConn.BeginTransaction();
			try
			{
				var existData = dbConn.Query<ExampleModel>("SELECT  * FROM ExampleModel WHERE name=@name", new { name = examModel.Name },transaction).ToList();
				if (existData == null || existData.Count==0)
				{
					result = dbConn.Execute("INSERT INTO ExampleModel(" +
						"name, detailUrl,category,categoryUrl,hotNum,downloadCount,needScore,devLanguage,downloadSize,pubdate,pubPersion, downloadUrl ) " +
						"VALUES (@Name, @DetailUrl,@Category,@CategoryUrl,@HotNum,@DownloadCount,@NeedScore,@DevLanguage,@DownloadSize,@Pubdate,@PubPersion, @DownloadUrl);", examModel,transaction);
					if (result > 0 && examModel.Tags != null && examModel.Tags.Count > 0)
					{
						foreach(var tag in examModel.Tags)
						{
							var existTag = dbConn.Query<TagModel>("SELECT * FROM TagModel WHERE tagName=@tagName;", new { tagName = tag },transaction).ToList();
							if(existTag==null || existTag.Count==0)
							{
								result = dbConn.Execute("INSERT INTO TagModel (tagName) VALUES (@tagName);", new { tagName = tag },transaction);
							}
							var em = dbConn.Query<ExampleModel>("SELECT  * FROM ExampleModel WHERE name=@name;", new { name = examModel.Name },transaction).SingleOrDefault();
							var tm = dbConn.Query<TagModel>("SELECT * FROM TagModel WHERE tagName=@tagName;", new { tagName = tag },transaction).SingleOrDefault();
							if(em!=null && tm!=null)
							{
								var existExampleMapTag = dbConn.Query<ExampleMapTag>("SELECT * FROM ExampleMapTag WHERE exampleId=@exampleId AND tagId=@tagId;", new { exampleId = em.Id,tagId=tm.Id },transaction).ToList();
								if (existExampleMapTag == null || existExampleMapTag.Count==0)
								{
									result = dbConn.Execute("INSERT INTO ExampleMapTag (exampleId,tagId) VALUES (@ExampleId,@TagId);", new ExampleMapTag() { ExampleId = em.Id, TagId = tm.Id },transaction);
								}
							}
						}
					}
					else
					{
						result = -1;
					}
				}
				transaction.Commit();
			}
			catch (Exception ex)
			{
				System.Diagnostics.Debug.WriteLine(ex.Message);
				transaction.Rollback();
			}
			dbConn.Close();
			return result;
		}


		/// <summary>
		/// 执行函数
		/// </summary>
		/// <param name="endIndex"></param>
		public static void RunStart(int endIndex)
		{
			for(int i=1;i<endIndex;i++)
			{
				Console.WriteLine("the page index is "+i);
				string requestUrl = string.Format(urlRoot, i);
				Console.WriteLine(requestUrl);
				string html = HtmlRequest(requestUrl).Result;
				Thread.Sleep(1000);

				List<ExampleModel> exampleModels = GetExampleData(html);

				foreach (var em in exampleModels)
				{
					int result = InsertDataBase(em);
					Console.WriteLine(dbType.ToString()+" Insert the data->" + em.Name + " result->" + result);
				};
			};
		}

		static void Main(string[] args)
		{
			int endIndex = 287;
			RunStart(endIndex);
			Console.WriteLine("End");
			Console.Read();
		}
	}
}
